package com.feib.soeasy.dao.hibernate;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.type.LongType;
import org.springframework.stereotype.Repository;

import com.feib.soeasy.dao.PaymentItemDao;
import com.feib.soeasy.model.PaymentItem;
import com.feib.soeasy.model.UploadFile;

/**
 * @title (#)PaymentItemHibernateDao.java<br>
 * @description 繳費單項目 Dao 實作<br>
 * @author Leo Lee<br>
 * @version 1.0.0 2010/11/26
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2010/11/26 create by Leo Lee<br>
 */
@Repository("paymentItemDao")
public class PaymentItemHibernateDao extends BaseHibernateDao<PaymentItem, Long> implements PaymentItemDao {
    /**
     * 繳費項目分類群組總覽
     * @param uploadFilePk uploadFilePk
     * @return 查詢結果物件
     */
    @Override
    public List queryPaymentItemGroupByUploadFilePk(
                    Long uploadFilePk) {

        StringBuffer hql = new StringBuffer();
        hql.append("select item.itemName, sum(item.amount) as amount, count(item.itemName) as items ");
        hql.append("from PaymentItem item ");
        hql.append("where item.payment in (");
        hql.append("select payment.paymentPk from Payment payment ");
        hql.append("where payment.uploadFile = :uploadFilePk ");
        hql.append(") ");
        hql.append("group by item.itemName ");
        Query itemQuery = getSession().createQuery(hql.toString());
        itemQuery.setParameter("uploadFilePk", uploadFilePk , LongType.INSTANCE);

        return itemQuery.list();
    }

    public void deletePaymentItemByUploadFile(UploadFile uploadFile)
    {
        StringBuffer hql = new StringBuffer();
        hql.append("delete PaymentItem as item ");
        hql.append("where item.payment in (");
        hql.append("select payment.paymentPk from Payment payment ");
        hql.append("where payment.uploadFile = :uploadFile ");
        hql.append(") ");

        Query itemQuery = getSession().createQuery(hql.toString());
        itemQuery.setParameter("uploadFile", uploadFile);
        itemQuery.executeUpdate();
    }

}
